from sqlalchemy import Column, Integer, String
from backend.database import Base

from datetime import datetime
from enum import Enum
from sqlalchemy import Column, Integer, String, Text, DateTime, Boolean, ForeignKey, Enum as SQLEnum, Index
from sqlalchemy.orm import relationship, declarative_base

Base = declarative_base()

class UserType(str, Enum):
    job_seeker = "job_seeker"
    recruiter = "recruiter"
    admin = "admin"

class CurrentStatus(str, Enum):
    in_job = "在职-考虑机会"
    unemployed = "离职-随时到岗"
    student = "在校学生"

class ApplicationStatus(str, Enum):
    submitted = "已投递"
    viewed = "已查看"
    interviewing = "面试中"
    accepted = "已录用"
    rejected = "已拒绝"

class ResourceCategory(str, Enum):
    resume_guide = "简历指导"
    interview_tips = "面试技巧"
    career_planning = "职业规划"
    regulations = "政策法规"

class User(Base):
    __tablename__ = "users"

    user_id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    username = Column(String(50), unique=True, nullable=False)
    password = Column(String(64), nullable=False)
    email = Column(String(100),  nullable=False)
    # user_type = Column(SQLEnum(UserType), nullable=False)
    user_type = Column(
        SQLEnum(UserType, values_callable=lambda x: [e.value for e in UserType], validate_strings=False),
        nullable=False
    )
    avatar_url = Column(String(255))
    phone = Column(String(20))
    created_at = Column(DateTime, default=datetime.utcnow)
    last_login = Column(DateTime)
    is_active = Column(Boolean, default=True)

    # Relationships
    job_postings = relationship("JobPosting", back_populates="poster")
    job_seeker_profile = relationship("JobSeekerProfile", uselist=False, back_populates="user")
    applications = relationship("Application", back_populates="applicant")
    sent_messages = relationship("Message", foreign_keys="Message.sender_id", back_populates="sender")
    received_messages = relationship("Message", foreign_keys="Message.receiver_id", back_populates="receiver")
    resources = relationship("Resource", back_populates="uploader")
    forum_posts = relationship("ForumPost", back_populates="author")

class JobPosting(Base):
    __tablename__ = "job_postings"

    job_id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey("users.user_id"), nullable=False)
    job_title = Column(String(100), nullable=False)
    company_name = Column(String(100), nullable=False)
    location = Column(String(100), nullable=False)
    salary_range = Column(String(50))
    job_description = Column(Text, nullable=False)
    requirements = Column(Text, nullable=False)
    post_date = Column(DateTime, default=datetime.utcnow)
    expiration_date = Column(DateTime)
    is_open = Column(Boolean, default=True)

    # Relationships
    poster = relationship("User", back_populates="job_postings")
    applications = relationship("Application", back_populates="job")
    messages = relationship("Message", back_populates="related_job")

    # Indexes
    __table_args__ = (
        Index("idx_job_title", "job_title"),
        Index("idx_location", "location"),
    )

class JobSeekerProfile(Base):
    __tablename__ = "job_seeker_profiles"

    profile_id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey("users.user_id"), unique=True, nullable=False)
    desired_position = Column(String(100), nullable=False)
    current_status = Column(SQLEnum(CurrentStatus, values_callable=lambda x: [e.value for e in CurrentStatus]), nullable=False)
    experience_years = Column(Integer)
    expected_salary = Column(String(50))
    education_level = Column(String(50))
    skills = Column(Text)
    last_updated = Column(DateTime, default=datetime.utcnow, onupdate=datetime.utcnow)

    # Relationships
    user = relationship("User", back_populates="job_seeker_profile")

class Application(Base):
    __tablename__ = "applications"

    application_id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    job_id = Column(Integer, ForeignKey("job_postings.job_id"), nullable=False)
    applicant_id = Column(Integer, ForeignKey("users.user_id"), nullable=False)
    apply_time = Column(DateTime, default=datetime.utcnow)
    status = Column(SQLEnum(ApplicationStatus, values_callable=lambda x: [e.value for e in ApplicationStatus]), default=ApplicationStatus.submitted)
    cover_letter = Column(Text)

    # Relationships
    job = relationship("JobPosting", back_populates="applications")
    applicant = relationship("User", back_populates="applications")

    # Indexes
    __table_args__ = (
        Index("idx_status", "status"),
    )

class Message(Base):
    __tablename__ = "messages"

    message_id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    sender_id = Column(Integer, ForeignKey("users.user_id"), nullable=False)
    receiver_id = Column(Integer, ForeignKey("users.user_id"), nullable=False)
    related_job_id = Column(Integer, ForeignKey("job_postings.job_id"))
    content = Column(Text, nullable=False)
    send_time = Column(DateTime, default=datetime.utcnow)
    is_read = Column(Boolean, default=False)

    # Relationships
    sender = relationship("User", foreign_keys=[sender_id], back_populates="sent_messages")
    receiver = relationship("User", foreign_keys=[receiver_id], back_populates="received_messages")
    related_job = relationship("JobPosting", back_populates="messages")

class Resource(Base):
    __tablename__ = "resources"

    resource_id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    title = Column(String(200), nullable=False)
    content = Column(Text, nullable=False)
    category = Column(SQLEnum(ResourceCategory, values_callable=lambda x: [e.value for e in ResourceCategory]), nullable=False)
    uploader_id = Column(Integer, ForeignKey("users.user_id"), nullable=False)
    upload_time = Column(DateTime, default=datetime.utcnow)
    view_count = Column(Integer, default=0)

    # Relationships
    uploader = relationship("User", back_populates="resources")

    # Indexes
    __table_args__ = (
        Index("idx_category", "category"),
    )

    @property
    def uploader_username(self):
        return self.uploader.username

class ForumPost(Base):
    __tablename__ = "forum_posts"

    post_id = Column(Integer, primary_key=True, index=True, autoincrement=True)
    user_id = Column(Integer, ForeignKey("users.user_id"), nullable=False)
    title = Column(String(200))
    content = Column(Text, nullable=False)
    post_time = Column(DateTime, default=datetime.utcnow)
    parent_post_id = Column(Integer, ForeignKey("forum_posts.post_id"))
    upvotes = Column(Integer, default=0)
    downvotes = Column(Integer, default=0)

    # Relationships
    author = relationship("User", back_populates="forum_posts")
    replies = relationship("ForumPost", back_populates="parent_post", remote_side=[parent_post_id])  # 一对多关系
    parent_post = relationship("ForumPost", remote_side=[post_id], back_populates="replies")  # 多对一关系

    # Indexes
    __table_args__ = (
        Index("idx_post_time", "post_time"),
    )